<?php

namespace app\store\controller\statistics;

use app\common\library\helper;
use app\store\controller\Controller;
use app\store\service\statistics\Data as StatisticsDataService;
use think\Db;
use app\store\model\store\Shop as ShopModel;

/**
 * 数据概况
 * Class Data
 * @package app\store\controller\statistics
 */
class Data extends Controller
{
    /* @var $statisticsDataService StatisticsDataService 数据概况服务类 */
    private $statisticsDataService;

    /**
     * 构造方法
     * @throws \app\common\exception\BaseException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function _initialize()
    {
        parent::_initialize();
        $this->statisticsDataService = new StatisticsDataService;
    }

    /**
     * 数据统计主页
     * @return mixed
     * @throws \think\Exception
     */
    public function index()
    {
        $coach = (new \app\store\model\Coach())->select();
        return $this->fetch('index', [
            'coachList' => $coach,
            // 数据概况
            'survey' => $this->statisticsDataService->getSurveyData(),
            // 近七日交易走势
            //'echarts7days' => $this->statisticsDataService->getTransactionTrend(),
            // 商品销售榜
            'goodsRanking' => $this->statisticsDataService->getGoodsRanking(),
            // 用户消费榜
            'userExpendRanking' => $this->statisticsDataService->geUserExpendRanking(),
            // 销售额走势图
            'echarts7days' => $this->shop($startDate = '', $endDate = '', $shop_id = '', 1),
            // 销售额走势图
            'shop7days' => $this->shop($startDate = '', $endDate = '', $shop_id = '', 1, 20),
            // 门店课时
            "storeCourse" => $this->course($startDate = '', $endDate = '', 1, $shop_id = ''),
            "shopList" => ShopModel::getAllList()
        ]);
    }

    /**
     * 数据概况API
     * @param null $startDate
     * @param null $endDate
     * @return array
     * @throws \think\Exception
     */
    public function survey($startDate = null, $endDate = null)
    {
        return $this->renderSuccess('', '',
            $this->statisticsDataService->getSurveyData($startDate, $endDate));
    }


    /**
     * @param $startDate
     * @param $endDate
     * @param $shop_id
     * @param $from
     * @param $type 20后台添加 10普通
     * @return array
     */
    public function shop($startDate = '', $endDate = '', $shop_id = '', $from = 0, $order_source = '')
    {
        if (!$startDate) {
            $start_time = date('Y-m-d', strtotime("-7 days", time()));
        } else {
            $start_time = $startDate;
        }
        if (!$endDate) {
            $end_time = date('Y-m-d', time());
        } else {
            $end_time = $endDate;
        }
        $date = $this->generate($start_time, $end_time);
        $sql = "select sum(yco.total_price) as total_price,from_unixtime(yco.create_time,'%Y-%m-%d') as `date`,count('order_id') as num from yoshop_course_order yco
         where yco.pay_status=20 and yco.is_delete=0 ";
        if ($shop_id) {
            $sql .= " and yco.shop_id=" . $shop_id;
        }
        if ($order_source) {
            $sql .= " and yco.order_source=" . $order_source;

        }
        $sql .= " and yco.create_time>=" . strtotime($start_time);
        $sql .= " and yco.create_time<=" . strtotime($end_time." 23:59:59 ");
        $sql .= " group by from_unixtime(yco.create_time,'%Y-%m-%d')";
        $list = Db::query($sql);

        $date_list = array_column($list, 'date');
        $date_list = array_combine($date_list, $list);

        // 成交额
        $order_total_price = [];
        $order_total = [];
        foreach ($date as $dt) {
            if (array_key_exists($dt, $date_list)) {
                $order_total_price[] = $date_list[$dt]['total_price'];
                $order_total[] = $date_list[$dt]['num'];
            } else {
                $order_total_price[] = 0;
                $order_total[] = 0;
            }
        }
        if ($from) {
            $data = ['date' => helper::jsonEncode($date), 'order_total_price' => helper::jsonEncode($order_total_price), 'order_total' => helper::jsonEncode($order_total)];
            return $data;
        }
        return $this->renderSuccess('', '', ['date' => $date, 'order_total_price' => $order_total_price, 'order_total' => $order_total]);
    }

    /**
     * 销售额数据下载
     * @param $startDate
     * @param $endDate
     * @param $shop_id
     * @param $order_source
     * @return void
     */
    public function shop_csv($startDate = '', $endDate = '', $shop_id = '', $order_source = '')
    {
        //export_excel
        if (!$startDate) {
            $start_time = date('Y-m-d', strtotime("-7 days", time()));
        } else {
            $start_time = $startDate;
        }
        if (!$endDate) {
            $end_time = date('Y-m-d', time());
        } else {
            $end_time = $endDate;
        }
        $sql = "select yco.total_price,from_unixtime(yco.create_time,'%Y-%m-%d') as `date`,count('order_id') as num from yoshop_course_order yco
         where yco.pay_status=20 and yco.is_delete=0 ";
        if ($shop_id) {
            $sql .= " and yco.shop_id=" . $shop_id;
        }
        $file_name = $start_time . '-' . $end_time . "销售额数据.csv";
        if ($order_source) {
            $sql .= " and yco.order_source=" . $order_source;
            $file_name = $start_time . '-' . $end_time . "线下销售额数据.csv";
        }
        $sql .= " and yco.create_time>=" . strtotime($start_time);
        $sql .= " and yco.create_time<=" . strtotime($end_time." 23:59:59 ");
        $sql .= " group by from_unixtime(yco.create_time,'%Y-%m-%d')";
        $list = Db::query($sql);

        $title = ['日期', '成交量', '成交额'];
        $data = [];
        foreach ($list as $value) {
            $data[] = [$value['date'], $value['num'], $value['total_price']];
        }

        export_excel($file_name, $title, $data);
    }

    /**
     * 全部数据
     * @param integer type 1次卡2团课卡
     * @return array|mixed
     */
    public function course($startDate = '', $endDate = '', $type = 1, $shop_id = null, $from = 0, $export = 0)
    {
        if (!$startDate) {
            $start_time = date('Y-m-d', strtotime("-7 days", time()));
        } else {
            $start_time = $startDate;
        }
        if (!$endDate) {
            $end_time = date('Y-m-d', time())." 23:59:59 ";
        } else {
            $end_time = $endDate." 23:59:59 ";
        }
        $sql = "select r.coach_id,r.`date`,r.shop_id,ycod.goods_name,ycod.nums,ycod.total_price,ycod.type,
       yss.shop_name,yc.real_name,r.course_order_id
from yoshop_course_reservation as r
left join yoshop_course_order yco on r.course_order_id = yco.order_id
left join yoshop_course_order_detail ycod on r.course_order_id = ycod.course_order_id
left join yoshop_store_shop yss on yss.shop_id=r.shop_id
left join yoshop_coach yc on r.coach_id = yc.coach_id
where r.status = 2 and yco.is_delete=0 ";
        if ($shop_id) {
            $sql .= " and r.shop_id={$shop_id}";
        }
        $sql .= " and ycod.`type`={$type}";
        $sql .= " and r.`date` >= '" . $start_time . "' ";
        $sql .= " and r.`date` <= '" . $end_time . "' ";
        $list = Db::query($sql);
        // 组合数据
        $price = $result = $nums = [];
        foreach ($list as $value) {
            $result[$value['date']][] = $value;
        }

        $dates = $this->generate($start_time, $end_time);
        foreach ($dates as $key => $date) {
            if (array_key_exists($date, $result)) {
                foreach ($result[$date] as $value) {
                    if (array_key_exists($key, $price)) {
                        $price[$key] += bcdiv($value['total_price'], $value['nums'], 2);
                    } else {
                        $price[$key] = bcdiv($value['total_price'], $value['nums'], 2);
                    }
                }
                $nums[$key] = count($result[$date]);
            } else {
                $price[$key] = 0;
                $nums[$key] = 0;
            }
        }
        if ($from) {
            return $this->renderSuccess('', '', ['date' => $dates, 'order_total_price' => $price, 'order_total' => $nums]);
        }
        if ($export) {
            $data = [];
            $title = ['日期', '课时消耗', '课时价值'];
            if ($type == 1) {
                $file_name = $start_time . '-' . $end_time . "门店私课课程数据.csv";
            } else {
                $file_name = $start_time . '-' . $end_time . "门店团课课程数据.csv";
            }

            foreach ($dates as $key => $date) {
                $data[] = [$date, $nums[$key], $price[$key]];
            }
            export_excel($file_name, $title, $data);
            exit();
        }
        return ['date' => helper::jsonEncode($dates), 'order_total_price' => helper::jsonEncode(array_values($price)), 'order_total' => helper::jsonEncode(array_values($nums))];
    }

    public function coach($startDate = '', $endDate = '', $coach_id = '', $export = 0)
    {
        if (!$startDate) {
            $start_time = date('Y-m-d', strtotime("-7 days", time()));
        } else {
            $start_time = $startDate;
        }
        if (!$endDate) {
            $end_time = date('Y-m-d', time())." 23:59:59 ";
        } else {
            $end_time = $endDate." 23:59:59 ";
        }
        $sql = "select r.coach_id,r.`date`,r.shop_id,ycod.goods_name,ycod.nums,ycod.total_price,ycod.type,
       yss.shop_name,yc.real_name,r.course_order_id
from yoshop_course_reservation as r
left join yoshop_course_order yco on r.course_order_id = yco.order_id
left join yoshop_course_order_detail ycod on r.course_order_id = ycod.course_order_id
left join yoshop_store_shop yss on yss.shop_id=r.shop_id
left join yoshop_coach yc on r.coach_id = yc.coach_id
where r.status = 2 and yco.is_delete=0  ";
        $sql .= " and r.`date` >= '" . $start_time . "' ";
        $sql .= " and r.`date` <= '" . $end_time . "' ";
        if ($coach_id) {
            $sql .= " and r.coach_id={$coach_id}";
        }
        $list = Db::query($sql);
        $dates = $this->generate($start_time, $end_time);
        // 如果为空
        if (empty($list)) {
            for ($i = 0; $i < count($dates); $i++) {
                $nums[] = 0;
                $price[] = 0;
            }
            if ($export) {
                $data = [];
                $title = ['日期', '教练', '课时消耗', '课时价值'];
                $file_name = $start_time . '-' . $end_time . "教练课程数据.csv";
                export_excel($file_name, $title, $data);
                exit();
            }
            return $this->renderSuccess('', '', ['date' => $dates, 'order_total_price' => $price, 'order_total' => $nums]);
        }

        $result = [];
        foreach ($list as $value) {
            if (array_key_exists($value['coach_id'], $result)) {
                $result[$value['coach_id']][] = $value;
            } else {
                $result[$value['coach_id']][] = $value;
            }
        }

        foreach ($result as $key => $value) {
            foreach ($value as $v) {
                $res[$key][$v['date']][] = $v;
            }
        }
        $response = [];
        foreach ($res as $key => $value) {
            foreach ($value as $k => $v) {
                foreach ($v as $v1) {
                    if (array_key_exists($key, $response)) {
                        if (array_key_exists($k, $response[$key])) {
                            $response[$key][$k]['order_total_price'] += bcdiv($v1['total_price'], $v1['nums'], 2);
                            $response[$key][$k]['order_total']++;
                        } else {
                            $response[$key][$k]['order_total_price'] = bcdiv($v1['total_price'], $v1['nums'], 2);
                            $response[$key][$k]['order_total'] = 1;
                            $response[$key][$k]['name'] = $v1['real_name'] . "({$v1['shop_name']})";
                        }

                    } else {
                        $response[$key][$k]['order_total_price'] = bcdiv($v1['total_price'], $v1['nums'], 2);
                        $response[$key][$k]['order_total'] = 1;
                        $response[$key][$k]['name'] = $v1['real_name'] . "({$v1['shop_name']})";
                    }
                }
            }
        }
        // 导出处理
        if ($export) {
            $data = [];
            $title = ['日期', '教练', '课时消耗', '课时价值'];
            $file_name = $start_time . '-' . $end_time . "教练课程数据.csv";

            foreach ($response as $value) {
                foreach ($value as $k => $v) {
                    $data[] = [$k, $v['name'], $v['order_total'], $v['order_total_price']];
                }
            }
            export_excel($file_name, $title, $data);
            exit();
        }

        $new=$names = $price = $nums = [];
        foreach ($response as $key => $value) {
            foreach ($value as $k=>$v){
                if (array_key_exists($k,$new)){
                    $new[$k]['order_total_price']+=$v['order_total_price'];
                    $new[$k]['order_total']+=$v['order_total'];
                }else{
                    $new[$k]['order_total_price']=$v['order_total_price'];
                    $new[$k]['order_total']=$v['order_total'];
                }
            }
        }

        foreach ($dates as $date) {
            if (array_key_exists($date, $new)) {
                $price[] = $new[$date]['order_total_price'];
                $nums[] = $new[$date]['order_total'];
            } else {
                $price[] = "0.00";
                $nums[] = 0;
            }
        }

        return $this->renderSuccess('', '', ['date' => $dates, 'order_total_price' => $price, 'order_total' => $nums, 'names' => $names]);

    }

    /**
     * @title 教练销售业绩
     * @author Qian
     * Time: 2023/6/8  9:45
     */
    public function coach_sale($startDate = '', $endDate = '', $coach_id = '', $export = 0)
    {
        if (!$startDate) {
            $start_time = date('Y-m-d', strtotime("-7 days", time()));
        } else {
            $start_time = $startDate;
        }
        if (!$endDate) {
            $end_time = date('Y-m-d', time())." 23:59:59 ";
        } else {
            $end_time = $endDate." 23:59:59 ";
        }
        //教练销售 sale_type = 2
        $sql = "select from_unixtime(o.create_time,'%Y-%m-%d') `date`,sale_id,pay_price,yc.real_name,yss.shop_name from yoshop_course_order o 
left join yoshop_coach yc on o.sale_id=yc.coach_id
left join yoshop_store_shop yss on yc.shop_id=yss.shop_id
where order_status in (10,30)
 and pay_status = 20 and o.is_delete = 0 and sale_type = 2";
        $sql .= " and o.create_time >= '" . strtotime($start_time) . "' ";
        $sql .= " and o.create_time <= '" . strtotime($end_time) . "' ";
        if ($coach_id) {
            $sql .= " and sale_id={$coach_id}";
        }
        $list = Db::query($sql);
        $dates = $this->generate($start_time, $end_time);
        // 如果为空
        $price = [];
        $nums = [];
        if (empty($list)) {
            for ($i = 0; $i < count($dates); $i++) {
                $nums[] = 0;
                $price[] = 0;
            }
            if ($export) {
                $data = [];
                $title = ['日期', '教练', '销售业绩', '销售数量'];
                $file_name = $start_time . '-' . $end_time . "教练销售数据.csv";

                export_excel($file_name, $title, $data);
                exit();
            }
            return $this->renderSuccess('', '', ['date' => $dates, 'order_total_price' => $price, 'order_total' => $nums]);
        }

        $result = [];
        foreach ($list as $value) {
            if (array_key_exists($value['sale_id'], $result)) {
                $result[$value['sale_id']][] = $value;
            } else {
                $result[$value['sale_id']][] = $value;
            }
        }

        foreach ($result as $key => $value) {
            foreach ($value as $v) {
                $res[$key][$v['date']][] = $v;
            }
        }
        $response = [];
        foreach ($res as $key => $value) {
            foreach ($value as $k => $v) {
                foreach ($v as $v1) {
                    if (array_key_exists($key, $response)) {
                        if (array_key_exists($k, $response[$key])) {
                            $response[$key][$k]['order_total_price'] += $v1['pay_price'];
                            $response[$key][$k]['order_total']++;
                        } else {
                            $response[$key][$k]['order_total_price'] = $v1['pay_price'];
                            $response[$key][$k]['order_total'] = 1;
                            $response[$key][$k]['name'] = $v1['real_name'] . "({$v1['shop_name']})";
                        }

                    } else {
                        $response[$key][$k]['order_total_price'] = $v1['pay_price'];
                        $response[$key][$k]['order_total'] = 1;
                        $response[$key][$k]['name'] = $v1['real_name'] . "({$v1['shop_name']})";
                    }
                }
            }
        }
        // 导出处理
        if ($export) {
            $data = [];
            $title = ['日期', '教练', '销售业绩', '销售数量'];
            $file_name = $start_time . '-' . $end_time . "教练销售数据.csv";

            foreach ($response as $value) {
                foreach ($value as $k => $v) {
                    $data[] = [$k, $v['name'], $v['order_total'], $v['order_total_price']];
                }
            }
            export_excel($file_name, $title, $data);
            exit();
        }

        $new = $names = $price = $nums = [];
        foreach ($response as $key => $value) {
            foreach ($value as $k=>$v){
                if (array_key_exists($k,$new)){
                    $new[$k]['order_total_price']+=$v['order_total_price'];
                    $new[$k]['order_total']+=$v['order_total'];
                }else{
                    $new[$k]['order_total_price']=$v['order_total_price'];
                    $new[$k]['order_total']=$v['order_total'];
                }
            }
        }

        foreach ($dates as $date) {
            if (array_key_exists($date, $new)) {
                $price[] = $new[$date]['order_total_price'];
                $nums[] = $new[$date]['order_total'];
            } else {
                $price[] = "0.00";
                $nums[] = 0;
            }
        }

        return $this->renderSuccess('', '', ['date' => $dates, 'order_total_price' => $price, 'order_total' => $nums, 'names' => $names]);

    }

    /**
     * 日历生成
     * @param $start_date
     * @param $end_date
     * @return array
     */
    public function generate($start_date, $end_date)
    {
        $start = strtotime($start_date);
        $end = strtotime($end_date);
        $result = [];
        for ($i = $start; $i <= $end; $i += 86400) {
            $result[] = date('Y-m-d', $i);
        }
        return $result;
    }


}